﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace zUfDsl.Common
{
    public class HandleExcel
    {

        #region 判断选择的文件是否真正的Excel文件
        /// <summary>
        /// 判断选择的文件是否真正的Excel文件
        /// </summary>
        /// <param name="FileName">文件路径</param>
        /// <returns></returns>
        public static FileExtension CheckTrueFileName(string FileName)
        {
            string path = FileName;
            System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Open, System.IO.FileAccess.ReadWrite);
            System.IO.BinaryReader r = new System.IO.BinaryReader(fs);
            string bx = " ";
            byte buffer;
            try
            {
                buffer = r.ReadByte();
                bx = buffer.ToString();
                buffer = r.ReadByte();
                bx += buffer.ToString();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
            r.Close();
            fs.Close();
            //真实的文件类型
            switch (bx)
            {
                case "3434":
                    return FileExtension.TXT;
                case "208207":
                case "8075":
                    return FileExtension.XLS;
                default:
                    return FileExtension.UnKnow;
            }
            //if (bx == "3434")
            //{ return FileExtension.TXT; }
            //else
            //{ return FileExtension.XLS; }
            //MessageBox.Show(bx);
            ////文件名，包括格式
            //MessageBox.Show(System.IO.Path.GetFileName(path));
            ////文件名， 不包括格式
            //MessageBox.Show(System.IO.Path.GetFileNameWithoutExtension(path));
            ////文件格式
            //MessageBox.Show(System.IO.Path.GetExtension(path));
        }
        #endregion 判断选择的文件是否真正的Excel文件

        #region 根据文件头与文件类型对照表
        /// <summary>
        /// 根据文件头与文件类型对照表
        /// </summary>
        public enum FileExtension
        {
            UnKnow = 0,
            JPG = 255216,
            GIF = 7173,
            BMP = 6677,
            PNG = 13780,
            COM = 7790,
            EXE = 7790,
            DLL = 7790,
            RAR = 8297,
            ZIP = 8075,
            XML = 6063,
            HTML = 6033,
            ASPX = 239187,
            CS = 117115,
            JS = 119105,
            //TXT = 210187,
            TXT = 3434,
            SQL = 255254,
            BAT = 64101,
            BTSEED = 10056,
            RDP = 255254,
            PSD = 5666,
            PDF = 3780,
            CHM = 7384,
            LOG = 70105,
            REG = 8269,
            HLP = 6395,
            DOC = 208207,
            XLS = 208207,
            DOCX = 208207,
            XLSX = 208207,
        }
        #endregion 根据文件头与文件类型对照表

        #region 获取Excel的数据
        /// <summary>
        /// 获取Excel的数据
        /// </summary>
        /// <param name="cSource">Excel文件路径</param>
        /// <param name="tbname">工作簿名称</param>
        /// <param name="f">进度提示窗体</param>
        /// <returns></returns>
        public static DataTable ReadExcelData(string cSource, string tbname)
        {

            string strConn;
            string path = cSource;
            if (path.Substring(path.Length - 3, 3) == "xls")
            {
                strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'", path);
            }
            else
            {
                strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1'";
            }
            //= @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + cSource + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
            Application.DoEvents();
            using (OleDbConnection cn = new OleDbConnection(strConn))
            {
                OleDbDataAdapter adp = new OleDbDataAdapter(
                "Select * from [" + tbname + "]", cn);
                Application.DoEvents();
                DataTable dt = new DataTable();
                adp.Fill(dt);
                Application.DoEvents();
                if (dt.Rows.Count == 0) { return dt; }
                var dtnew = new DataTable();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var dtr = dt.Rows[0];
                    var columnname = dtr[j].ToString();
                    columnname = columnname.Trim().Replace("/", "").Replace("\\", "");
                    dtnew.Columns.Add(columnname);
                }
                for (int i = 1; i < dt.Rows.Count; i++)
                {
                    var dtr = dt.Rows[i];
                    dtnew.Rows.Add(dtr.ItemArray);
                }
                return dtnew;
            }
        }
        #endregion

        #region 获取Excel文件的所有工作表
        /// <summary>
        /// 获取Excel文件的所有工作表
        /// </summary>
        /// <param name="path">路径</param>
        /// <returns>返回的Excel工作表名称为TABLENAME</returns>
        public static DataTable ReadExcelSheet(string path)
        {
            //2003（Microsoft.Jet.Oledb.4.0）
            string strConn;
            if (path.Substring(path.Length - 3, 3) == "xls")
            {
                strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", path);
            }
            else
            {
                strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
            }
            //2010（Microsoft.ACE.OLEDB.12.0）
            //string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", path);
            //string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
            using (OleDbConnection connection = new
                       OleDbConnection(strConn))
            {
                connection.Open();
                DataTable schemaTable = connection.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables,
                    new object[] { null, null, null, "TABLE" });
                DataTable dt = new DataTable();
                dt.Columns.Add("TABLENAME");
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    string s = schemaTable.Rows[i]["TABLENAME"].ToString();
                    if (s.Substring(0, 1) == "'" && s.Substring(s.Length - 1, 1) == "'")
                    { s = s.Substring(1, s.Length - 2); }
                    if (s.Length >= 2)
                    {
                        if (s.Substring(s.Length - 1, 1) == "$" && s.Substring(s.Length - 2, 2) != "$$")
                        {
                            DataRow dtr = dt.NewRow();
                            dtr["TABLENAME"] = s;
                            dt.Rows.Add(dtr);
                        }
                    }
                }
                connection.Close();
                return dt;
            }
        }
        #endregion 获取Excel文件的所有工作表

        #region 检查datatable中的列是否存在
        /// <summary>
        /// 检查datatable中的列是否存在
        /// </summary>
        public static string CheckExcelColumns(DataTable dtsource, List<string> colLists)
        {
            var err = "";
            var columns = dtsource.Columns;
            foreach (var col in colLists)
            {
                if (!columns.Contains(col))
                { err += string.Format("[{0}]", col); }
            }
            if (err.Length > 0) { err = string.Format("以下列不存在：{0}", err); }
            return err;
        }
        #endregion
    }
}
